package org.kjtc.mapper;

import org.apache.ibatis.annotations.*;
import org.kjtc.entity.Sprint;
import org.kjtc.entity.SprintCommon;

import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/12/4 0004.
 */
@Mapper
public interface SprintMapper {

    @Select("select * from sprint where ProductVersionID=#{ProductVersionID} order by SprintName")
     List<Sprint> getSprint(String ProductVersionID);

    @Select("SELECT SprintStartDTTM,SprintEndDTTM,TO_DAYS(NOW())-TO_DAYS(SprintStartDTTM) OverdueWork,TO_DAYS(SprintEndDTTM)-TO_DAYS(NOW()) SurplusWork FROM sprint WHERE SprintID=#{sprintID} ")
     List<SprintCommon> getSprintDateByID(String sprintID);

    @Select("SELECT SUM(a.AllWork) allWork,b.doneWork doneWork FROM " +
            "(SELECT ss.StateName,SUM(bl.ExpectedWorkDate) AllWork FROM sprint sp,backlog bl,module md,state ss WHERE sp.SprintID=md.SprintID AND bl.BacklogStatus=ss.StateID AND bl.ModuleID=md.ModuleID AND sp.SprintID=#{sprintID} GROUP BY ss.StateName)a, " +
            "(SELECT sp.SprintID,ss.StateName,SUM(bl.ExpectedWorkDate) doneWork FROM sprint sp,backlog bl,module md,state ss WHERE sp.SprintID=md.SprintID AND bl.BacklogStatus=ss.StateID AND bl.ModuleID=md.ModuleID AND sp.SprintID=#{sprintID} AND bl.BacklogStatus in(3,4))b " +
            "GROUP BY b.SprintID")
     List<SprintCommon> getDoneWorkBySprintId(String sprintID);

    @Select("SELECT a.UserName AS UserName,SUM(a.toDoWork) AS toDoWork,SUM(a.doingWork) AS doingWork,SUM(a.doneWork) AS doneWork,SUM(a.publishWork) AS publishWork " +
            "FROM(" +
            "SELECT usr.UserName AS UserName, sum(bkl.ExpectedWorkDate) AS toDoWork, 0 AS doingWork, 0 AS doneWork, 0 AS publishWork FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID =#{sprintID} AND bkl.BacklogStatus = 1 GROUP BY usr.UserID UNION ALL " +
            "SELECT usr.UserName AS UserName, 0 AS toDoWork,sum(bkl.ExpectedWorkDate) AS doingWork, 0 AS doneWork, 0 AS publishWork FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID =#{sprintID} AND bkl.BacklogStatus = 2 GROUP BY usr.UserID UNION ALL " +
            "SELECT usr.UserName AS UserName, 0 AS toDoWork, 0 AS doingWork, sum(bkl.ExpectedWorkDate) AS doneWork, 0 AS publishWork FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID =#{sprintID} AND bkl.BacklogStatus = 3 GROUP BY usr.UserID UNION ALL " +
            "SELECT usr.UserName AS UserName, 0 AS toDoWork, 0 AS doingWork, 0 AS doneWork, sum(bkl.ExpectedWorkDate) AS publishWork FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID =#{sprintID} AND bkl.BacklogStatus = 4 GROUP BY usr.UserID)a " +
            "GROUP BY a.UserName")
     List<Map<String, Object>> getBackLogStateBySprintId(String sprintID);


    @Insert("insert into sprint (SprintID,SprintName,SprintNameSign,SprintStartDTTM,SprintEndDTTM,ProductVersionID,SprintDescription,SprintStatus,CreateUser,CreateDTTM,UpdateUser,UpdateDTTM) " +
            "values(#{SprintID},#{SprintName},#{SprintNameSign},#{SprintStartDTTMStr},#{SprintEndDTTMStr},#{ProductVersionID},#{SprintDescription},#{SprintStatus},#{CreateUser},NOW(),#{CreateUser},NOW())")
     int addSprint(Sprint sprint);


    @Select("select * from sprintstatistics WHERE SprintID=#{sprintID} GROUP BY Date ORDER BY date")
     List<Map<String, Object>> getBackLogCountByEveryDay(String sprintID);


    @Select("select * from sprint where SprintID=#{sprintID}")
     List<Sprint> getSprintMessageBySprintID(String sprintID);

    @Select("SELECT SUM(a.toDoCnt+a.doingCnt) noDone" +
            "FROM(\n" +
            "SELECT usr.UserName AS UserName, COUNT(bkl.BacklogID) AS toDoCnt, 0 AS doingCnt, 0 AS doneCnt, 0 AS publishCnt FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID = 1 AND bkl.BacklogStatus = 1 GROUP BY usr.UserID UNION ALL " +
            "SELECT usr.UserName AS UserName, 0 AS toDoCnt, COUNT(bkl.BacklogID) AS doingCnt, 0 AS doneCnt, 0 AS publishCnt FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID = 1 AND bkl.BacklogStatus = 2 GROUP BY usr.UserID UNION ALL " +
            "SELECT usr.UserName AS UserName, 0 AS toDoCnt, 0 AS doingCnt, COUNT(bkl.BacklogID) AS doneCnt, 0 AS publishCnt FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID = 1 AND bkl.BacklogStatus = 3 GROUP BY usr.UserID UNION ALL " +
            "SELECT usr.UserName AS UserName, 0 AS toDoCnt, 0 AS doingCnt, 0 AS doneCnt, COUNT(bkl.BacklogID) AS publishCnt FROM userinfo usr,module md,backlog bkl WHERE usr.UserID = bkl.UserID AND bkl.ModuleID = md.ModuleID AND md.SprintID = 1 AND bkl.BacklogStatus = 4 GROUP BY usr.UserID)a ")
     List<Map<String, Object>> getBackLogStateByEveryDay(String sprintID);

    @Update("update sprint set SprintName=#{SprintName},SprintNameSign=#{SprintNameSign},SprintStartDTTM=#{SprintStartDTTMStr},SprintEndDTTM=#{SprintEndDTTMStr},SprintDescription=#{SprintDescription},SprintStatus=#{SprintStatus},UpdateUser=#{UpdateUser},UpdateDTTM=NOW() WHERE SprintID=#{SprintID}")
     int updateSprintBySprintID(Sprint sprint);

    @Delete("DELETE FROM sprint WHERE SprintID=#{sprintID}")
     int deleteSprintById(String sprintID);

    @Select("SELECT pd.ProductName,pv.ProductVersionName,sp.SprintName FROM productversion pv,product pd,sprint sp " +
            "WHERE sp.SprintID=#{sprintID} AND sp.ProductVersionID=pv.ProductVersionID AND pv.ProductID=pd.ProductID")
    List<Sprint> updateCurrentTitle(String sprintID);

    @SelectProvider(type =AllProvider.class, method = "getProductNameByProductVersionID")
    List<Sprint> getProductNameByProductVersionID(String ProductVersionID);




}
